package lingnan.cn.edu.service;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;

import lingnan.cn.edu.BaseAndImpl.MilkManageService;
import lingnan.cn.edu.User.Milkteainf;
import lingnan.cn.edu.util.DbAccess;

public class MilkManageServiceImpl implements MilkManageService{
	
	/**
	 * 查找全部奶茶信息
	 */
	@Override
	public List<Milkteainf> query() {
		List<Milkteainf> milkteas = new ArrayList<Milkteainf>();
		Connection conn = DbAccess.getConnection();
		String SQL = "select * from product";		
		PreparedStatement prep = null;
		ResultSet rs=null;
		try {

			prep = conn.prepareStatement(SQL);
			rs = prep.executeQuery();
			while (rs.next()) {
				Milkteainf milktea = new Milkteainf();
				milktea.setName(rs.getString("name"));
				milktea.setId(rs.getInt("id"));
				milktea.setPrice(rs.getInt("price"));
				milktea.setImg(rs.getString("img"));
				milktea.setType(rs.getString("type"));
				milkteas.add(milktea);
			}
		} catch (SQLException e) {

			e.printStackTrace();
		} finally {
			DbAccess.release(conn, prep, rs);
		}

		return milkteas;
	}
	
	
	/**
	 * 按奶茶名称或奶茶价格查找
	 */
	@Override
	public List<Milkteainf> findMilkTea(String name,String type){
		List<Milkteainf> milkteas = new ArrayList<Milkteainf>();
		Connection conn = DbAccess.getConnection();
		String SQL = "null";		
		PreparedStatement prep = null;
		ResultSet rs=null;
		try {
			if(type==null) {
				SQL = "select * from product where name like ?";
				prep = conn.prepareStatement(SQL);
				prep.setString(1, "%"+name+"%");
			}else if(name==null) {
				SQL = "select * from product where type = ?";
				prep = conn.prepareStatement(SQL);
				prep.setString(1, type);
			}else {
				SQL = "select * from product where name like ? and type = ?";
				prep = conn.prepareStatement(SQL);
				prep.setString(1, "%"+name+"%");
				prep.setString(2, type);
			}
			rs = prep.executeQuery();
			while (rs.next()) {
				Milkteainf milktea = new Milkteainf();
				milktea.setName(rs.getString("name"));
				milktea.setId(rs.getInt("id"));
				milktea.setPrice(rs.getInt("price"));
				milktea.setImg(rs.getString("img"));
				milktea.setType(rs.getString("type"));
				milkteas.add(milktea);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DbAccess.release(conn, prep, rs);
		}
		return milkteas;
	}


	@Override
	public int addMilkTea(Milkteainf milkteainf) {
		int flag = 0;
		Connection conn = DbAccess.getConnection();
		int maxId = 0;
		String SQL1 = "select MAX(id) from product";  //获取最大ID
		String SQL2 = "insert into product(id,name,price,img,type) values(?,?,?,?,?)";	//插入	
		PreparedStatement prep = null;
		PreparedStatement maxPrep = null;
		ResultSet rs = null;
		try {
			maxPrep = conn.prepareStatement(SQL1);
			rs = maxPrep.executeQuery();
			if(rs.next())
				maxId = rs.getInt("MAX(id)")+1;
			prep = conn.prepareStatement(SQL2);
			prep.setInt(1,maxId);
			prep.setString(2,milkteainf.getName());
			prep.setInt(3,milkteainf.getPrice());
			prep.setString(4,milkteainf.getImg());
			prep.setString(5, milkteainf.getType());
			prep.executeUpdate();
			flag = 1;
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DbAccess.release(conn, prep);
		}
		
		return flag;
	}


	@Override
	public int deleteByName(String name) {
		int flag = 0;
		Connection conn = DbAccess.getConnection();
		String SQL = "delete from product where name = ?";		
		PreparedStatement prep = null;
		try {
			prep = conn.prepareStatement(SQL);
			prep.setString(1,name);			
			prep.executeUpdate();
			flag = 1;
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DbAccess.release(conn, prep);
		}		
		return flag;
	}


	@Override
	public int updateMilkTea(Milkteainf milkteainf,String name) {
		int flag = 0;
		Connection conn = DbAccess.getConnection();
		String SQL = "update product set name=?,price=?,img=?,type=? where name=?";		
		PreparedStatement prep = null;
		try {
			prep = conn.prepareStatement(SQL);
			prep.setString(1,milkteainf.getName());
			prep.setInt(2,milkteainf.getPrice());
			prep.setString(3,milkteainf.getImg());
			prep.setString(4,milkteainf.getType());
			prep.setString(5,name);
			prep.executeUpdate();
			flag = 1;
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DbAccess.release(conn, prep);
		}
		
		return flag;
	}


	@Override
	public HashMap<String, Milkteainf> milkteaMap(List<Milkteainf> milkteas) {
		HashMap<String, Milkteainf> milkteaMap = new HashMap<String, Milkteainf>();
		/*int idcount = 0;
		Connection conn = DbAccess.getConnection();
		String SQL = "select count(mteaId) from milktea";		
		PreparedStatement prep = null;
		ResultSet rs = null;
		try {
			prep = conn.prepareStatement(SQL);
			rs = prep.executeQuery();
			if(rs.next())
				idcount = rs.getInt("count(mteaId)");
		}catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DbAccess.release(conn, prep,rs);
		}*/
		for(int i=0;i<milkteas.size();i++) {
			milkteaMap.put(String.valueOf(i),milkteas.get(i));
		}
		return milkteaMap;
	}
	
}
